<?php

class FDMSCases extends Zend_Db_Table_Abstract
{
    protected $_name = 'cases';
    protected $_rowClass = 'FDMSCase';

    public function getPreregCases()
    {
        $results = array ();

        try
        {
            $r = $this->fetchAll("status = 'PREREG'");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getPendingCases()
    {
        $results = array ();

        try
        {
            $r = $this->fetchAll("status = 'OPEN'");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getFinishedCases()
    {
        $results = array ();

        try
        {
            $r = $this->fetchAll("status = 'FINISHED'");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    // pass in the comma-separated list of FD ids (FH-specific ones)
    // and use the IN where clause
    public function getPreregCasesForFDMSFuneralDirIds($FDMS_fd_ids)
    {
        $results = array ();
        try
        {
            $r = $this->fetchAll("status = 'PREREG' and FDMS_funeral_director_id IN ($FDMS_fd_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }
        return $results;
    }

	public function getPreregCasesAssignedToGroup($group_ids_str)
	{
        $results = array ();
        try
        {
            $r = $this->fetchAll("status = 'PREREG' and group_number IN ($group_ids_str)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }
        return $results;
	}
	
    public function markPreregCasesAsClaimed($case_ids_str)
    {
        $update = array ();
        $update['status'] = 'CLAIMED';

        if ( empty($case_ids_str))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        $results = array ();
        try
        {
            $r = $this->update($update, "id in ($case_ids_str)");
            $results['rows_affected'] = $r;
            $results['status'] = "SUCCEEDED";
        }
        catch(Exception $e)
        {
            $results['rows_affected'] = 0;
            $results['status'] = "FAILED";
            $results['reason'] = "".$e;
        }

        return $results;
    }

    public function getFDMSCaseNumbersForFuneralDirIds($FDMS_fd_ids)
    {
        $results = array ();
        try
        {
            $r = $this->fetchAll("(status = 'OPEN' or status='FINISHED') and FDMS_funeral_director_id IN ($FDMS_fd_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = array ('FDMS_case_num'=>$row->FDMS_case_num, 'FDMS_vitals_id'=>$row->FDMS_vitals_id);
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }
        return $results;
    }

    public function getOpenCasesForFDMSFuneralDirIds($FDMS_fd_ids)
    {
        $results = array ();
        try
        {
            $r = $this->fetchAll("status = 'OPEN' and FDMS_funeral_director_id IN ($FDMS_fd_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }
        return $results;
    }

    public function getFinishedCasesForFDMSFuneralDirIds($FDMS_fd_ids)
    {
        $results = array ();
        try
        {
            $r = $this->fetchAll("status = 'FINISHED' and FDMS_funeral_director_id IN ($FDMS_fd_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }
        return $results;
    }

    public function getPreregCasesForBranches($branch_ids)
    {
        $results = array ();

        if ( empty($branch_ids))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'PREREG' and branch_id in($branch_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getOpenAndFinishedCaseIdsForBranches($branch_numbers)
    {
        $results = array ();

        if ( empty($branch_ids))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        try
        {
            $r = $this->fetchAll("(status = 'OPEN' or status = 'FINISHED') and branch_num in ($branch_numbers)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = array ('FDMS_case_num'=>$row['FDMS_case_num'], 'FDMS_vitals_id'=>$row['FDMS_vitals_id'], 'status'=>$row['status']);
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getOpenAndFinishedCaseIdsForFD($arranger_id)
    {
        $results = array ();

        if ( empty($arranger_id))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        try
        {
            $r = $this->fetchAll("(status = 'OPEN' or status = 'FINISHED') and FDMS_funeral_director_id = $arranger_id");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = array ('FDMS_case_num'=>$row['FDMS_case_num'], 'FDMS_vitals_id'=>$row['FDMS_vitals_id'], 'status'=>$row['status']);
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }
	
    public function getOpenCasesForBranches($branch_ids)
    {
        $results = array ();

        if ( empty($branch_ids))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'OPEN' and branch_id in ($branch_ids)");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getFinishedCasesForBranches($branch_ids)
    {
        $results = array ();

        if ( empty($branch_ids))
        {
            $results['status'] = "FAILED_BAD_PARAM";
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'FINISHED' and branch_id in [$branch_ids]");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getCasesForFuneralDirector($VAS_fd_id)
    {
        $results = array ();

        if (! isset ($VAS_fd_id) && empty($VAS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $r = $this->fetchAll("fd_id = $VAS_fd_id");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getCasesForFDMSFuneralDirId($FDMS_fd_id)
    {
        $results = array ();

        if (! isset ($FDMS_fd_id) && empty($FDMS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            $results['rows'] = array ();
            return $results;
        }

        try
        {
            $r = $this->fetchAll("FDMS_funeral_director_id = $FDMS_fd_id");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getCasesForFDMSFuneralDirIdAtBranch($FDMS_fd_id, $branch_number)
    {
        $results = array ();

        if (! isset ($FDMS_fd_id) && empty($FDMS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            $results['rows'] = array ();
            return $results;
        }

        try
        {
            $r = $this->fetchAll("FDMS_funeral_director_id = $FDMS_fd_id and branch_num='$branch_number'");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getPreregCasesForFuneralDirector($FDMS_fd_id)
    {
        $results = array ();

        if ( empty($FDMS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'PREREG' and FDMS_funeral_director_id = '$FDMS_fd_id'");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function countOpenCasesForBranchNumber($branchId)
    {
        $select = $this->select();
        $select->from($this->_name, 'COUNT(*) AS num');
        $select->where("branch_id = '$branchId' and status = 'OPEN'");
        return $this->fetchRow($select)->num;
    }

    public function getPendingCasesForFuneralDirectorAtBranch($VAS_fd_id)
    {
        $results = array ();

        if ( empty($VAS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'OPEN' and fd_id = $VAS_fd_id");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getFinishedCasesForFuneralDirector($VAS_fd_id)
    {
        $results = array ();

        if ( empty($VAS_fd_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $r = $this->fetchAll("status = 'FINISHED' and fd_id = $VAS_fd_id");
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
            $rows[] = $row->toArray();
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getCaseInfo($VAS_case_id)
    {
        $results = array ();

        if ( empty($VAS_case_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $result = $this->fetchRow("id=$VAS_case_id");
            $row = $result->toArray();
            $results['status'] = 'SUCCEEDED';
            $results['rows'] = array ($row);
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function updateCurrentModuleForFDMSVitalsId($current_module, $FDMS_vitalsId)
    {
        $results = array ();

        if ( empty($FDMS_vitalsId))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile = array ();
        $caseProfile['current_module'] = $current_module;

        try
        {
            $result = $this->update($caseProfile, "FDMS_vitals_id=$FDMS_vitalsId");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function getCaseInfoForFDMSVitalsId($FDMS_vitals_id)
    {
        $results = array ();

        if ( empty($FDMS_vitals_id))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $result = $this->fetchRow("FDMS_vitals_id=$FDMS_vitals_id");
            if ($result)
            {
                $row = $result->toArray();
                $results['status'] = 'SUCCEEDED';
                $results['rows'] = array ($row);
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows'] = array ();
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
            $results['reason'] = $e;
        }

        return $results;
    }

    public function getCaseInfoForFDMSCaseNum($FDMS_case_num)
    {
        $results = array ();

        if ( empty($FDMS_case_num))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $result = $this->fetchRow("FDMS_case_num='$FDMS_case_num'");
            if ($result)
            {
                $row = $result->toArray();
                $results['status'] = 'SUCCEEDED';
                $results['rows'] = array ($row);
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows'] = array ();
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
            $results['reason'] = $e;
        }

        return $results;
    }

    public function setChoiceForCase($choice, $case_num)
    {
        $results = array ();

        if ( empty($case_num) || empty($choice))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile = array ();
        $caseProfile['choice'] = $choice;

        try
        {
            $result = $this->update($caseProfile, "FDMS_case_num='$case_num'");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['reason'] = ''.$e;
            $results['rows'] = array ();
        }

        return $results;
    }

    public function setFinancialForCase($financials, $paid_in_full, $case_num)
    {
        $results = array ();

        if ( empty($case_num) || empty($financials))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile = array ();
        $caseProfile['funding_option'] = $financials;
        $caseProfile['paid_in_full'] = $paid_in_full;

        try
        {
            $result = $this->update($caseProfile, "FDMS_case_num='$case_num'");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['reason'] = ''.$e;
            $results['rows'] = array ();
        }

        return $results;
    }

    public function setTributeForCase($tribute, $case_num)
    {
        $results = array ();

        if ( empty($case_num) || empty($tribute))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile = array ();
        $caseProfile['tribute_chosen'] = $tribute;

        try
        {
            $result = $this->update($caseProfile, "FDMS_case_num='$case_num'");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function setStatusForCase($case_status, $case_num)
    {
        $results = array ();

        if ( empty($case_num) || empty($case_status))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile = array ();
        $caseProfile['status'] = $case_status;

        try
        {
            $result = $this->update($caseProfile, "FDMS_case_num='$case_num'");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

    public function insertCase($caseProfile, $branch, $branchnum)
    {
        $results = array ();

        if ( empty($caseProfile) || empty($branch) || empty($branchnum))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        $caseProfile['branch_id'] = $branch;
        $caseProfile['branch_num'] = $branchnum;

        try
        {
            $result = $this->insert($caseProfile);
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = $result;
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
			$results['reason'] = "".$e;
        }

        return $results;
    }

    public function updateCase($caseProfile, $case_num)
    {
        $results = array ();

        if ( empty($caseProfile) || empty($case_num))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $result = $this->update($caseProfile, "FDMS_case_num='$case_num'");
            if ($result > 0)
            {
                $results['status'] = 'SUCCEEDED';
                $results['rows_affected'] = $result;
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows_affected'] = 0;
				$results['notes'] = "case number is $case_num";
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
			$results['reason'] = "".$e;
        }

        return $results;
    }
	
    public function getCase($FDMS_case_num)
    {
        $results = array ();

        if ( empty($FDMS_case_num))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }

        try
        {
            $result = $this->fetchRow("FDMS_case_num='$FDMS_case_num'");
            if ($result)
            {
                $row = $result->toArray();
                $results['status'] = 'SUCCEEDED';
                $results['rows'] = array ($row);
            }
            else
            {
                $results['status'] = 'FAILED';
                $results['rows'] = array ();
            }
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
            $results['reason'] = $e;
        }

        return $results;
    }

	// returned array includes revenue for case and branch numbers
    public function getFinishedCasesForArrangerIds( $FDids )
    {
        $results = array ();

        if ( empty($FDids))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }
		
        try
        {
			$select = $this->select();
			$select->from($this, 
					array('FDMS_case_num', 'FDMS_funeral_director_id', 'total_revenue'))
			       ->where("status = 'FINISHED' And FDMS_funeral_director_id IN ($FDids)");
		
			$rows = $this->fetchAll($select);
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
			{
				$array_row = array();
				$array_row['FDMS_case_num'] = $row->FDMS_case_num;
				$array_row['branch_number'] = $row->branch_number;
				$array_row['total_revenue'] = $row->total_revenue;
				$rows[] = $array_row;				
			}
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
			$results['reason'] = "".$e;
        }

        return $results;
    }

	// returned array includes revenue for case, arranger license
    public function getFinishedCasesForBranch( $branch_number )
    {
        $results = array ();

        if(empty($branch_number))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }
		
        try
        {
			$select = $this->select();
			$select->from($this, 
					array('FDMS_case_num', 'FDMS_funeral_director_id', 'branch_num', 'total_revenue'))
			       ->where("status = 'FINISHED' And branch_num = $branch_number");
		
			$r = $this->fetchAll($select);
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
			{
				$array_row = array();
				$array_row['FDMS_case_num'] = $row->FDMS_case_num;
				$array_row['branch_number'] = $row->branch_num;
				$array_row['total_revenue'] = $row->total_revenue;
				$rows[] = $array_row;				
			}
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }

	// returned array includes revenue for case, arranger license, and branch number
    public function getFinishedCasesForRegion( $region )
    {
        $results = array ();

        if(empty($region))
        {
            $results['status'] = 'FAILED_BAD_PARAM';
            return $results;
        }
		
		$funeralHomes = new FuneralHomes();
		$rs = $funeralHomes->getFuneralHomesInRegion( $region );
		if($rs['status'] != 'SUCCEEDED')
			return $rs;
		
		$fhs = $rs['rows'];
		$branches_str = implode(",", $fhs);
				
        try
        {
			$select = $this->select();
			$select->from($this, 
					array('FDMS_case_num', 'FDMS_funeral_director_id', 'branch_num', 'total_revenue'))
			       ->where("status = 'FINISHED' And branch_num IN ($branches_str)");
		
			$r = $this->fetchAll($select);
            $results['status'] = 'SUCCEEDED';
            $rows = array ();
            foreach ($r as $row)
			{
				$array_row = array();
				$array_row['FDMS_case_num'] = $row->FDMS_case_num;
				$array_row['branch_number'] = $row->branch_num;
				$array_row['total_revenue'] = $row->total_revenue;
				$rows[] = $array_row;				
			}
            $results['rows'] = $rows;
        }
        catch(Exception $e)
        {
            $results['status'] = 'FAILED';
            $results['rows'] = array ();
        }

        return $results;
    }
	
	public function getCaseStatsForArranger( $arranger_id )
	{
		$select = $this->select();
		$select->from( $this, array( 
			'count(total_revenue) as num_cases',
			'avg(total_revenue) as avg_revenue',
			'sum(total_revenue) as sum_revenue',
			'min(total_revenue) as min_revenue',
			'max(total_revenue) as max_revenue'
		 ))->where("status = 'FINISHED' and total_revenue > 0 and FDMS_funeral_director_id = $arranger_id");
		
		$returned_rows = array();
		$rows = $this->fetchAll($select)->toArray();
		$returned_rows = array(
			'num_cases'=>$rows[0]['num_cases'],
			'min_revenue'=>sprintf("%0.2f",$rows[0]['min_revenue']),
			'max_revenue'=>sprintf("%0.2f",$rows[0]['max_revenue']),
			'sum_revenue'=>sprintf("%0.2f",$rows[0]['sum_revenue']),
			'avg_revenue'=>sprintf("%0.2f",$rows[0]['avg_revenue'])
		);
		
		return $returned_rows;
	}
	
	public function getCaseStatsForBranch( $branch_number )
	{
		$select = $this->select();
		$select->from( $this, array( 
			'count(total_revenue) as num_cases',
			'sum(total_revenue) as sum_revenue',
			'avg(total_revenue) as avg_revenue',
			'min(total_revenue) as min_revenue',
			'max(total_revenue) as max_revenue'
		 ))->where("status = 'FINISHED' and total_revenue > 0 and branch_num = $branch_number");
		
		$returned_rows = array();
		$rows = $this->fetchAll($select)->toArray();
		
		$returned_rows = array();
		$rows = $this->fetchAll($select)->toArray();
		$returned_rows = array(
			'num_cases'=>$rows[0]['num_cases'],
			'min_revenue'=>sprintf("%0.2f",$rows[0]['min_revenue']),
			'max_revenue'=>sprintf("%0.2f",$rows[0]['max_revenue']),
			'sum_revenue'=>sprintf("%0.2f",$rows[0]['sum_revenue']),
			'avg_revenue'=>sprintf("%0.2f",$rows[0]['avg_revenue'])
		);
		
		return $returned_rows;
	}
	
	public function getCaseStatsForRegion( $region )
	{
		$funeralHomes = new FuneralHomes();
		$rs = $funeralHomes->getFuneralHomesInRegion( $region );
		if($rs['status'] != 'SUCCEEDED')
			return $rs;
		
		$fhs = $rs['rows'];
		if(count($fhs)>0)
		{
		$branches_str = implode(",", $fhs);		
		$select = $this->select();
		$select->from( $this, array( 
			'count(total_revenue) as num_cases',
			'sum(total_revenue) as sum_revenue',
			'avg(total_revenue) as avg_revenue',
			'min(total_revenue) as min_revenue',
			'max(total_revenue) as max_revenue'
		 ))->where("status = 'FINISHED' and total_revenue > 0 and branch_num IN ($branches_str)");
		
		$returned_rows = array();
		$rows = $this->fetchAll($select)->toArray();
		$returned_rows = array(
			'num_cases'=>$rows[0]['num_cases'],
			'min_revenue'=>sprintf("%0.2f",$rows[0]['min_revenue']),
			'max_revenue'=>sprintf("%0.2f",$rows[0]['max_revenue']),
			'sum_revenue'=>sprintf("%0.2f",$rows[0]['sum_revenue']),
			'avg_revenue'=>sprintf("%0.2f",$rows[0]['avg_revenue'])
		);
		}
		else
		{
			$returned_rows = array(
				'num_cases'=>"0",
				'min_revenue'=>"-1",
				'max_revenue'=>"-1",
				'sum_revenue'=>"-1",
				'avg_revenue'=>"-1"
			);
		}
		return $returned_rows;
	}
	
	public function getCaseStatsForCompany()
	{
		$select = $this->select();
		$select->from( $this, array( 
			'count(total_revenue) as num_cases',
			'sum(total_revenue) as sum_revenue',
			'avg(total_revenue) as avg_revenue',
			'min(total_revenue) as min_revenue',
			'max(total_revenue) as max_revenue'
		 ))->where("status = 'FINISHED' and total_revenue > 0");
		
		$returned_rows = array();
		$rows = $this->fetchAll($select)->toArray();
		$returned_rows = array(
			'num_cases'=>$rows[0]['num_cases'],
			'min_revenue'=>sprintf("%0.2f",$rows[0]['min_revenue']),
			'max_revenue'=>sprintf("%0.2f",$rows[0]['max_revenue']),
			'sum_revenue'=>sprintf("%0.2f",$rows[0]['sum_revenue']),
			'avg_revenue'=>sprintf("%0.2f",$rows[0]['avg_revenue'])
		);
		
		return $returned_rows;
	}
}

?>
